﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetMyClassMail]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetMyClassMail];
GO
CREATE PROCEDURE [dbo].[sproc_GetMyClassMail] 
    @UserName nvarchar(255),
    @ClassID int,
    @DisplayType int
/*

============================================================
功能:    得到此组中我的邮件列表
参数:
    @UserName nvarchar(255)    :    用户名
    @ClassID int            :    组（类）ID
    @DisplayType int        :    显示类型
备注:
============================================================

*/
As
---收件箱
IF @DisplayType = 0
    select
            a.DocID, 
            a.DocTitle, 
            a.DocContent, 
            c.readed, 
            c.sender, 
            a.DocAddedDate, 
            c.receiver, 
            b.Email, 
            (select count(*) from dbo.uds_files k where k.docid = a.docid) as Attachs, 
            '' dd
        from
            dbo.UDS_Document a
            left outer join dbo.UDS_STAFF b on a.DocAddedBy = b.Staff_Name
            inner join dbo.UDS_MailAttach c on a.DocID = c.DocID
        where
            c.receiver = @username 
            and a.ClassID = @ClassID 
            and a.DocType = 1 
            and a.DocDeletion = 0 
            and c.position = 1
        order by
            a.DocAddedDate desc

--发件箱
IF  @DisplayType = 1
    select
            a.DocID, 
            a.DocTitle, 
            a.DocContent, 
            c.readed, 
            c.allreceiver, 
            a.DocAddedDate, 
            c.sender, 
            b.Email, 
            (select count(*) from uds_files k where k.docid = a.docid) as Attachs, 
            '' dd
        from
            dbo.UDS_Document a
            left outer join dbo.UDS_STAFF b on a.DocAddedBy = b.Staff_Name
            inner join dbo.UDS_MailAttach c on a.DocID = c.DocID
        where
            c.sender = @username 
            and a.ClassID = @ClassID 
            and a.DocType = 1 
            and a.DocDeletion = 0 
            and c.position = 2 
        order by a.DocAddedDate desc

--回收站
IF  @DisplayType = 2
    select 
            a.DocID, 
            a.DocTitle, 
            a.DocContent, 
            c.readed, 
            c.allreceiver, 
            a.DocAddedDate, 
            people = 
                case c.position 
                    when 1 then c.receiver 
                    when 2 then c.sender 
                end, 
            b.Email, 
            (select count(*) from uds_files k where k.docid = a.docid) as Attachs, 
            '' dd
        from 
            dbo.UDS_Document a
            left outer join dbo.UDS_STAFF b on a.DocAddedBy = b.Staff_Name
            inner join dbo.UDS_MailAttach c on a.DocID = c.DocID
        where 
            c.sender = @username 
            and a.ClassID = @ClassID 
            and a.DocType = 1 
            and a.DocDeletion = 1
        order by a.DocAddedDate desc
GO